{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对backtrader进行改善优化：\n",
    "\n",
    "一：继承创建手续费stampDutyCommissionScheme类，根据中国股市设置佣金最低额度5元，已经印花税卖出单边收取模式。\n",
    "\n",
    "二：创建策略类Strategy_LJT：新策略可继承使用\n",
    "1.策略中增加回测订单信息输出。\n",
    "2.策略中增加成交结果信息输出。\n",
    "\n",
    "三：创建整体运行函数\n",
    "backtrader_run(code = \"600000\",\n",
    "                   start=\"2020-01-01\",\n",
    "                   end = \"2020-11-01\",\n",
    "                   strategy = Strategy_LJT,\n",
    "                   data = None,\n",
    "                   cash = 10000,\n",
    "                   commission = 0.0002,\n",
    "                   stamp_duty = 0.001,\n",
    "                   )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import backtrader_LJT as btl\n",
    "import backtrader as bt\n",
    "%matplotlib inline\n",
    "class my_strategy(btl.Strategy_LJT):\n",
    "   # 全局设定交易策略的参数\n",
    "    params = (\n",
    "        ('maperiod', 10),\n",
    "    )\n",
    "\n",
    "    def __init__(self):\n",
    "        # 指定价格序列\n",
    "        self.dataclose = self.datas[0].close\n",
    "        self.datavolume = self.datas[0].volume\n",
    "        # 初始化交易指令、买卖价格和手续费\n",
    "        self.order = None\n",
    "        self.buyprice = None\n",
    "        self.buycomm = None\n",
    "\n",
    "        # 添加移动均线指标，内置了talib模块\n",
    "        self.sma = bt.indicators.SimpleMovingAverage(\n",
    "            self.dataclose, period=self.params.maperiod)        \n",
    "        \n",
    "        self.sma_5 = bt.indicators.SimpleMovingAverage(\n",
    "            self.dataclose, period= 5)      \n",
    "        self.sma_20 = bt.indicators.SimpleMovingAverage(\n",
    "            self.dataclose, period= 20)      \n",
    "        self.sma_120 = bt.indicators.SimpleMovingAverage(\n",
    "            self.dataclose, period= 120)      \n",
    "        # 添加移动均线指标，内置了talib模块\n",
    "        self.volume_sma_5 = bt.indicators.SimpleMovingAverage(\n",
    "            self.datavolume, period= 5)\n",
    "        self.volume_sma_20 = bt.indicators.SimpleMovingAverage(\n",
    "            self.datavolume, period= 20)\n",
    "        self.volume_sma_120 = bt.indicators.SimpleMovingAverage(\n",
    "            self.datavolume, period= 120)\n",
    "\n",
    "    def next(self):\n",
    "        if self.order:  # 检查是否有指令等待执行,\n",
    "            print(self.order)\n",
    "            return\n",
    "        # 检查是否持仓\n",
    "        if not self.position:  # 没有持仓\n",
    "            # 执行买入条件判断：价格小于（20/120）均线，大于5日均线，且量小于均线（20/120）\n",
    "            if self.dataclose[0] < self.sma_20[0] and self.dataclose[0] < self.sma_120[0] and self.dataclose[0] > self.sma_5[0]\\\n",
    "                and self.data_volume[0] < self.volume_sma_20[0] and self.data_volume[0] < self.volume_sma_120[0]:\n",
    "                # 执行买入\n",
    "                self.order = self.buy(size=10000)\n",
    "        else:\n",
    "            # 执行卖出条件判断：价格高于（20/5）均线，小于5日均线，且量小于均线（20/5）\n",
    "            if self.dataclose[0] > self.sma_20[0]  and self.dataclose[0] < self.sma_5[0]\\\n",
    "                and self.data_volume[0] > self.volume_sma_20[0] and self.data_volume[0] > self.volume_sma_5[0]:                # 执行卖出\n",
    "                self.order = self.sell(size=10000)\n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "btl.backtrader_run(code=\"159928\",\n",
    "                   strategy=my_strategy,\n",
    "                   start=\"2014-6-1\",\n",
    "                   end='2020-12-30',\n",
    "                   cash=30000,\n",
    "                   commission=0.0002,\n",
    "#                    trans_rate=0.00002,\n",
    "                   stamp_duty=0.001)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts\n",
    "ts.get_k_data('600000', autype='qfq', start='2017-01-01', end='') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "event_label = pd.read_csv(\"..\\chinascope_news_data\\event_label.csv\")\n",
    "event_label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "industry_label = pd.read_csv(\"..\\chinascope_news_data\\industry_label.csv\")\n",
    "industry_label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "company_label = pd.read_csv(\"..\\chinascope_news_data\\company_label.csv\")\n",
    "company_label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "news_info = pd.read_csv(\"..\\chinascope_news_data\\news_info.csv\")\n",
    "news_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "file=open(\"..\\chinascope_news_data\\company_label.csv\",'r',encoding='gbk')\n",
    "reder = csv.reader(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开数据库连接\n",
    "db = pymysql.connect(\"localhost\",\"root\",\"111111\")\n",
    "# 使用 cursor() 方法创建一个游标对象 cursor\n",
    "db.cursor().execute('database dbname1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "from flask_sqlalchemy import SQLAlchemy\n",
    "from flask import Flask\n",
    "import pymysql\n",
    "# from sqlalchemy import Datetime\n",
    "\n",
    "app = Flask(__name__)\n",
    "\n",
    "app.config[\"SQLALCHEMY_DATABASE_URI\"] = \"sqlite:///\" + \"news.db\"\n",
    "app.config[\"SQLALCHEMY_TRACK_MODIFICATIONS\"] = False\n",
    "app.config[\"SECRET_KEY\"] = '123456'\n",
    "db = SQLAlchemy(app)################################################\n",
    "\n",
    "class Company_label(db.Model):\n",
    "    __tablename__ = \"company_label\"\n",
    "    index = db.Column(db.Integer,primary_key=True) #主键\n",
    "    news_id = db.Column(db.Integer,primary_key=True) #主键\n",
    "    news_time = db.Column(db.DateTime,nullable=False)\n",
    "    sec_code = db.Column(db.String(20),nullable=False)\n",
    "    company_name = db.Column(db.String(20),nullable=False)\n",
    "    relevance = db.Column(db.Float,nullable=False)\n",
    "    pos = db.Column(db.Float,nullable=False)\n",
    "    neg = db.Column(db.Float,nullable=False)\n",
    "    neu = db.Column(db.Float,nullable=False)\n",
    "\n",
    "class Event_label(db.Model):\n",
    "    __tablename__ = \"event_label\"   \n",
    "    index = db.Column(db.Integer,primary_key=True) #主键\n",
    "    news_id = db.Column(db.Integer,primary_key=True) #主键\n",
    "    news_time = db.Column(db.DateTime,nullable=False)\n",
    "    event_code = db.Column(db.String(20),nullable=False)\n",
    "    event_name = db.Column(db.String(20),nullable=False)\n",
    "\n",
    "class Industry_label(db.Model):\n",
    "    __talbename__ = \"industry_label\"\n",
    "    index = db.Column(db.Integer,primary_key=True) #主键\n",
    "    news_id = db.Column(db.Integer,primary_key=True) #主键\n",
    "    news_time = db.Column(db.DateTime,nullable=False)\n",
    "    industry_code = db.Column(db.String(20),nullable=False)\n",
    "    industry_name = db.Column(db.String(20),nullable=False)\n",
    "    relevance = db.Column(db.Float,nullable=False)\n",
    "\n",
    "\n",
    "class News_info(db.Model):\n",
    "    __talbename__ = \"news_info\"\n",
    "    index = db.Column(db.Integer,primary_key=True) #主键\n",
    "    news_id = db.Column(db.Integer,primary_key=True) #主键\n",
    "    news_time = db.Column(db.DateTime,nullable=False)\n",
    "    news_title = db.Column(db.String(80),nullable=False)\n",
    "    news_url = db.Column(db.String(256),nullable=False)\n",
    "    news_source = db.Column(db.String(20))\n",
    "    pos = db.Column(db.Float,nullable=False)\n",
    "    neg = db.Column(db.Float,nullable=False)\n",
    "    neu = db.Column(db.Float,nullable=False)\n",
    "    \n",
    "db.create_all()##########################创建数据库\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3071: DtypeWarning: Columns (4) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import csv\n",
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy import Column,VARCHAR,Integer\n",
    "\n",
    "Base = declarative_base()\n",
    "engine = create_engine('sqlite:///news.db')\n",
    "Base.metadata.create_all(engine)\n",
    "file_name = ('..\\chinascope_news_data\\event_label.csv')#csv转换为数据库db ，耗时间长！！！！！！！！！！\n",
    "merge_dt = pd.read_csv(file_name,chunksize = 1000000)#,index_col=[\"news_id\"]\n",
    "# for i in range(1): \n",
    "#     df = merge_dt.get_chunk()\n",
    "for df in merge_dt:    \n",
    "    df.to_sql(con=engine,name=Event_label.__tablename__,if_exists='append')\n",
    "    \n",
    "file_name = ('..\\chinascope_news_data\\Company_label.csv')\n",
    "merge_dt = pd.read_csv(file_name,chunksize = 1000000)\n",
    "# for i in range(1): \n",
    "#     df = merge_dt.get_chunk()\n",
    "for df in merge_dt: \n",
    "    df.to_sql(con=engine,name=Company_label.__tablename__,if_exists='append')\n",
    "    \n",
    "file_name = ('../chinascope_news_data/Industry_label.csv')\n",
    "merge_dt = pd.read_csv(file_name,chunksize = 1000000)\n",
    "# for i in range(1): \n",
    "#     df = merge_dt.get_chunk()\n",
    "for df in merge_dt:    \n",
    "    df.to_sql(con=engine,name=Industry_label.__tablename__,if_exists='append')\n",
    "    \n",
    "file_name = ('../chinascope_news_data\\\\news_info.csv')\n",
    "merge_dt = pd.read_csv(file_name,chunksize = 1000000)\n",
    "# for i in range(1): \n",
    "#     df = merge_dt.get_chunk()\n",
    "for df in merge_dt:    \n",
    "    df.to_sql(con=engine,name=News_info.__tablename__,if_exists='append')\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = Company_label.query.filter(Company_label.company_name == \"勤上股份\",Company_label.news_time >= datetime(2020,5,15))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = Company_label.query.filter(Company_label.news_id == 21829371)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in a:\n",
    "    print(str(i.news_id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = Company_label.query.filter(Company_label.company_name == \"勤上股份\",Company_label.news_time > datetime(2020,6,30,19,40,19,99)\n",
    "                              ,Company_label.news_time < datetime(2020,6,30,19,40,20,00))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "21829375勤上股份2020-06-30 19:40:20\n"
     ]
    }
   ],
   "source": [
    "for i in a:\n",
    "    print(str(i.news_id)+i.company_name+str(i.news_time))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "21829375勤上股份2020-06-30 19:40:20\n",
      "news_id=21829375，company=勤上股份,time=2020-06-30 19:40:20,title=勤上股份被实施退市风险警示 终止收购爱迪教育\n",
      "21831558勤上股份2020-06-30 20:36:20\n",
      "news_id=21831558，company=勤上股份,time=2020-06-30 20:36:20,title=勤上股份连亏2年 7月1日起变更为“*ST勤上”\n"
     ]
    }
   ],
   "source": [
    "a = Company_label.query.filter(Company_label.company_name == \"勤上股份\",Company_label.news_time > datetime(2020,6,30,19)\n",
    "                              ,Company_label.news_time < datetime(2020,6,30,20,40,20,00))\n",
    "for i in a:\n",
    "    print(str(i.news_id)+i.company_name+str(i.news_time))    \n",
    "    print(f\"news_id={i.news_id}，company={i.company_name},time={i.news_time},title={News_info.query.filter(News_info.news_id==i.news_id)[0].news_title}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = Company_label.query.filter(Company_label.sec_code >= \"603888\",Company_label.news_time > datetime(2020,6,30,19,40,19,99)\n",
    "                              ,Company_label.news_time < datetime(2020,6,30,19,40,20,00))\n",
    "for i in a:\n",
    "    print(str(i.news_id)+,+i.company_name+str(i.news_time))    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'SQLAlchemy' object has no attribute 'close'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-172-168085b004b5>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m: 'SQLAlchemy' object has no attribute 'close'"
     ]
    }
   ],
   "source": [
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "开始插入\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\anaconda3\\lib\\site-packages\\sqlalchemy\\sql\\crud.py:801: SAWarning: Column 'event_label.index' is marked as a member of the primary key for table 'event_label', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed.  Primary key columns typically may not store NULL. Note that as of SQLAlchemy 1.1, 'autoincrement=True' must be indicated explicitly for composite (e.g. multicolumn) primary keys if AUTO_INCREMENT/SERIAL/IDENTITY behavior is expected for one of the columns in the primary key. CREATE TABLE statements are impacted by this change as well on most backends.\n",
      "  util.warn(msg)\n"
     ]
    },
    {
     "ename": "StatementError",
     "evalue": "(builtins.TypeError) SQLite DateTime type only accepts Python datetime and date objects as input.\n[SQL: INSERT INTO event_label (news_id, news_time, event_code, event_name) VALUES (?, ?, ?, ?)]\n[parameters: [{'news_id': '6827815', 'event_code': 'DB002062', 'news_time': '2017-01-01 00:04:00', 'event_name': '减税政策'}]]",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[0;32m   1204\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1205\u001b[1;33m             \u001b[0mcontext\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconstructor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdialect\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1206\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\default.py\u001b[0m in \u001b[0;36m_init_compiled\u001b[1;34m(cls, dialect, connection, dbapi_connection, compiled, parameters)\u001b[0m\n\u001b[0;32m    838\u001b[0m                     \u001b[1;32mif\u001b[0m \u001b[0mkey\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mprocessors\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 839\u001b[1;33m                         \u001b[0mparam\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mprocessors\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcompiled_params\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    840\u001b[0m                     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\dialects\\sqlite\\base.py\u001b[0m in \u001b[0;36mprocess\u001b[1;34m(value)\u001b[0m\n\u001b[0;32m    770\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 771\u001b[1;33m                 raise TypeError(\n\u001b[0m\u001b[0;32m    772\u001b[0m                     \u001b[1;34m\"SQLite DateTime type only accepts Python \"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: SQLite DateTime type only accepts Python datetime and date objects as input.",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mStatementError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-116-4a1260dfe19f>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      9\u001b[0m             \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0madd\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mhotel\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     10\u001b[0m         \u001b[1;31m# return render_template('/test/test-data.html',reader = reader)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 11\u001b[1;33m         \u001b[0mdb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     12\u001b[0m         \u001b[0mrender_template\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'/test/sucess.html'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mcontent\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'插入成功'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\orm\\scoping.py\u001b[0m in \u001b[0;36mdo\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m    161\u001b[0m \u001b[1;32mdef\u001b[0m \u001b[0minstrument\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    162\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 163\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mgetattr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mregistry\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    164\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    165\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mdo\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36mcommit\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1040\u001b[0m                 \u001b[1;32mraise\u001b[0m \u001b[0msa_exc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mInvalidRequestError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"No transaction is begun.\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1041\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1042\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtransaction\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1043\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1044\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mprepare\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36mcommit\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    502\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_assert_active\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mprepared_ok\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    503\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_state\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mPREPARED\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 504\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_prepare_impl\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    505\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    506\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_parent\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnested\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36m_prepare_impl\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    481\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_is_clean\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    482\u001b[0m                     \u001b[1;32mbreak\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 483\u001b[1;33m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mflush\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    484\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    485\u001b[0m                 raise exc.FlushError(\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36mflush\u001b[1;34m(self, objects)\u001b[0m\n\u001b[0;32m   2521\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2522\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_flushing\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2523\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_flush\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobjects\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2524\u001b[0m         \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2525\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_flushing\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36m_flush\u001b[1;34m(self, objects)\u001b[0m\n\u001b[0;32m   2662\u001b[0m         \u001b[1;32mexcept\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2663\u001b[0m             \u001b[1;32mwith\u001b[0m \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msafe_reraise\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2664\u001b[1;33m                 \u001b[0mtransaction\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrollback\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0m_capture_exception\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2665\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2666\u001b[0m     def bulk_save_objects(\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\util\\langhelpers.py\u001b[0m in \u001b[0;36m__exit__\u001b[1;34m(self, type_, value, traceback)\u001b[0m\n\u001b[0;32m     66\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_exc_info\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mNone\u001b[0m  \u001b[1;31m# remove potential circular references\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     67\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwarn_only\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 68\u001b[1;33m                 compat.raise_(\n\u001b[0m\u001b[0;32m     69\u001b[0m                     \u001b[0mexc_value\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     70\u001b[0m                 )\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\util\\compat.py\u001b[0m in \u001b[0;36mraise_\u001b[1;34m(***failed resolving arguments***)\u001b[0m\n\u001b[0;32m    176\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    177\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 178\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    179\u001b[0m         \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    180\u001b[0m             \u001b[1;31m# credit to\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\orm\\session.py\u001b[0m in \u001b[0;36m_flush\u001b[1;34m(self, objects)\u001b[0m\n\u001b[0;32m   2622\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_warn_on_events\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2623\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2624\u001b[1;33m                 \u001b[0mflush_context\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2625\u001b[0m             \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2626\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_warn_on_events\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\orm\\unitofwork.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    420\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    421\u001b[0m             \u001b[1;32mfor\u001b[0m \u001b[0mrec\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mtopological\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msort\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdependencies\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mpostsort_actions\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 422\u001b[1;33m                 \u001b[0mrec\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    423\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    424\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mfinalize_flush_changes\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\orm\\unitofwork.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, uow)\u001b[0m\n\u001b[0;32m    584\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    585\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0muow\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 586\u001b[1;33m         persistence.save_obj(\n\u001b[0m\u001b[0;32m    587\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmapper\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    588\u001b[0m             \u001b[0muow\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstates_for_mapper_hierarchy\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmapper\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\orm\\persistence.py\u001b[0m in \u001b[0;36msave_obj\u001b[1;34m(base_mapper, states, uowtransaction, single)\u001b[0m\n\u001b[0;32m    237\u001b[0m         )\n\u001b[0;32m    238\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 239\u001b[1;33m         _emit_insert_statements(\n\u001b[0m\u001b[0;32m    240\u001b[0m             \u001b[0mbase_mapper\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    241\u001b[0m             \u001b[0muowtransaction\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\orm\\persistence.py\u001b[0m in \u001b[0;36m_emit_insert_statements\u001b[1;34m(base_mapper, uowtransaction, cached_connections, mapper, table, insert, bookkeeping)\u001b[0m\n\u001b[0;32m   1133\u001b[0m                     )\n\u001b[0;32m   1134\u001b[0m                 \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1135\u001b[1;33m                     result = cached_connections[connection].execute(\n\u001b[0m\u001b[0;32m   1136\u001b[0m                         \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1137\u001b[0m                     )\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, object_, *multiparams, **params)\u001b[0m\n\u001b[0;32m   1012\u001b[0m             )\n\u001b[0;32m   1013\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1014\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1015\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1016\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\sql\\elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[1;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[0;32m    296\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    297\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 298\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    299\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    300\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[1;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[0;32m   1125\u001b[0m             )\n\u001b[0;32m   1126\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1127\u001b[1;33m         ret = self._execute_context(\n\u001b[0m\u001b[0;32m   1128\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1129\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecution_ctx_cls\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_init_compiled\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[0;32m   1205\u001b[0m             \u001b[0mcontext\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconstructor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdialect\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1206\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1207\u001b[1;33m             self._handle_dbapi_exception(\n\u001b[0m\u001b[0;32m   1208\u001b[0m                 \u001b[0me\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtext_type\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1209\u001b[0m             )\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[1;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[0;32m   1509\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0me\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1510\u001b[0m             \u001b[1;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1511\u001b[1;33m                 util.raise_(\n\u001b[0m\u001b[0;32m   1512\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0me\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1513\u001b[0m                 )\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\util\\compat.py\u001b[0m in \u001b[0;36mraise_\u001b[1;34m(***failed resolving arguments***)\u001b[0m\n\u001b[0;32m    176\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    177\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 178\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    179\u001b[0m         \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    180\u001b[0m             \u001b[1;31m# credit to\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[0;32m   1203\u001b[0m                 \u001b[0mconn\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_revalidate_connection\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1204\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1205\u001b[1;33m             \u001b[0mcontext\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconstructor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdialect\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1206\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1207\u001b[0m             self._handle_dbapi_exception(\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\default.py\u001b[0m in \u001b[0;36m_init_compiled\u001b[1;34m(cls, dialect, connection, dbapi_connection, compiled, parameters)\u001b[0m\n\u001b[0;32m    837\u001b[0m                 \u001b[1;32mfor\u001b[0m \u001b[0mkey\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mpositiontup\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    838\u001b[0m                     \u001b[1;32mif\u001b[0m \u001b[0mkey\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mprocessors\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 839\u001b[1;33m                         \u001b[0mparam\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mprocessors\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcompiled_params\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    840\u001b[0m                     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    841\u001b[0m                         \u001b[0mparam\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcompiled_params\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\sqlalchemy\\dialects\\sqlite\\base.py\u001b[0m in \u001b[0;36mprocess\u001b[1;34m(value)\u001b[0m\n\u001b[0;32m    769\u001b[0m                 }\n\u001b[0;32m    770\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 771\u001b[1;33m                 raise TypeError(\n\u001b[0m\u001b[0;32m    772\u001b[0m                     \u001b[1;34m\"SQLite DateTime type only accepts Python \"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    773\u001b[0m                     \u001b[1;34m\"datetime and date objects as input.\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mStatementError\u001b[0m: (builtins.TypeError) SQLite DateTime type only accepts Python datetime and date objects as input.\n[SQL: INSERT INTO event_label (news_id, news_time, event_code, event_name) VALUES (?, ?, ?, ?)]\n[parameters: [{'news_id': '6827815', 'event_code': 'DB002062', 'news_time': '2017-01-01 00:04:00', 'event_name': '减税政策'}]]"
     ]
    }
   ],
   "source": [
    "print('开始插入')\n",
    "with open('..\\chinascope_news_data\\event_label1.csv','r',encoding='utf-8') as f:   \n",
    "    f.readline()  # skip header\n",
    "    reader = csv.reader(f)\n",
    "\n",
    "    if reader != None:\n",
    "        for i in reader:\n",
    "            hotel = Event_label(news_id=i[0],news_time=i[1],event_code=i[2],event_name=i[3])\n",
    "            db.session.add(hotel)\n",
    "        # return render_template('/test/test-data.html',reader = reader)\n",
    "        db.session.commit()\n",
    "        render_template('/test/sucess.html',content='插入成功')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#导入pymysql方法\n",
    "import pymysql\n",
    "\n",
    "\n",
    "#连接数据库\n",
    "config = {'host':'',\n",
    "          'port':3306,\n",
    "          'user':'evdata',\n",
    "          'passwd':'',\n",
    "          'charset':'utf8mb4',\n",
    "          'local_infile':1\n",
    "          }\n",
    "conn = pymysql.connect(**config)\n",
    "cur = conn.cursor()\n",
    "\n",
    "\n",
    "#load_csv函数，参数分别为csv文件路径，表名称，数据库名称\n",
    "def load_csv(csv_file_path,table_name,database='evdata'):\n",
    "    #打开csv文件\n",
    "    file = open(csv_file_path, 'r',encoding='utf-8')\n",
    "    #读取csv文件第一行字段名，创建表\n",
    "    reader = file.readline()\n",
    "    b = reader.split(',')\n",
    "    colum = ''\n",
    "    for a in b:\n",
    "        colum = colum + a + ' varchar(255),'\n",
    "    colum = colum[:-1]\n",
    "    #编写sql，create_sql负责创建表，data_sql负责导入数据\n",
    "    create_sql = 'create table if not exists ' + table_name + ' ' + '(' + colum + ')' + ' DEFAULT CHARSET=utf8'\n",
    "    data_sql = \"LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\\\r\\\\n' IGNORE 1 LINES\" % (csv_filename,table_name)\n",
    " \n",
    "    #使用数据库\n",
    "    cur.execute('use %s' % database)\n",
    "    #设置编码格式\n",
    "    cur.execute('SET NAMES utf8;')\n",
    "    cur.execute('SET character_set_connection=utf8;')\n",
    "    #执行create_sql，创建表\n",
    "    cur.execute(create_sql)\n",
    "    #执行data_sql，导入数据\n",
    "    cur.execute(data_sql)\n",
    "    conn.commit()\n",
    "    #关闭连接\n",
    "    conn.close()\n",
    "    cur.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# python + pymysql 创建数据库 \n",
    "import pymysql\n",
    "\n",
    "# 创建连接\n",
    "conn = pymysql.connect(host='localhost',user='root',password='123456',charset='utf8mb4')\n",
    "# 创建游标\n",
    "cursor = conn.cursor()\n",
    " \n",
    "# 创建数据库的sql(如果数据库存在就不创建，防止异常)\n",
    "sql = \"CREATE DATABASE IF NOT EXISTS db_name\" \n",
    "# 执行创建数据库的sql\n",
    "cursor.execute(sql)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据库连接失败！\n"
     ]
    },
    {
     "ename": "UnicodeDecodeError",
     "evalue": "'gbk' codec can't decode byte 0x91 in position 360: illegal multibyte sequence",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mUnicodeDecodeError\u001b[0m                        Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-1-0fcbdab16b58>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     27\u001b[0m         \u001b[0mfilename\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfile\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msplit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'.'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m  \u001b[1;31m#获取剔除后缀的名称\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     28\u001b[0m         \u001b[0mfilename\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m'data_'\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0mfilename\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 29\u001b[1;33m         \u001b[0mf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfile\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mencoding\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'gbk'\u001b[0m\u001b[1;33m)\u001b[0m  \u001b[1;31m#用pandas读取文件，得到pandas框架格式的数据\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     30\u001b[0m         \u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtolist\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m  \u001b[1;31m#获取表格数据内的列标题文字数据\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     31\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36mparser_f\u001b[1;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, dialect, error_bad_lines, warn_bad_lines, delim_whitespace, low_memory, memory_map, float_precision)\u001b[0m\n\u001b[0;32m    674\u001b[0m         )\n\u001b[0;32m    675\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 676\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0m_read\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    677\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    678\u001b[0m     \u001b[0mparser_f\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__name__\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m_read\u001b[1;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[0;32m    446\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    447\u001b[0m     \u001b[1;31m# Create the parser.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 448\u001b[1;33m     \u001b[0mparser\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mTextFileReader\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfp_or_buf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    449\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    450\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mchunksize\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0miterator\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, f, engine, **kwds)\u001b[0m\n\u001b[0;32m    878\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"has_index_names\"\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"has_index_names\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    879\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 880\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_make_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    881\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    882\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m_make_engine\u001b[1;34m(self, engine)\u001b[0m\n\u001b[0;32m   1112\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_make_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"c\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1113\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"c\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1114\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mCParserWrapper\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1115\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1116\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"python\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\anaconda3\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, src, **kwds)\u001b[0m\n\u001b[0;32m   1889\u001b[0m         \u001b[0mkwds\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"usecols\"\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0musecols\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1890\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1891\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_reader\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mparsers\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mTextReader\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msrc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1892\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munnamed_cols\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_reader\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munnamed_cols\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1893\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader.__cinit__\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._get_header\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._tokenize_rows\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.raise_parser_error\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mUnicodeDecodeError\u001b[0m: 'gbk' codec can't decode byte 0x91 in position 360: illegal multibyte sequence"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "#pd.set_option()就是pycharm输出控制显示的设置\n",
    "pd.set_option('expand_frame_repr', False)#True就是可以换行显示。设置成False的时候不允许换行\n",
    "pd.set_option('display.max_columns', None)# 显示所有列\n",
    "#pd.set_option('display.max_rows', None)# 显示所有行\n",
    "pd.set_option('colheader_justify', 'centre')# 显示居中\n",
    "\n",
    "try:\n",
    "    conn = pymysql.connect(host='localhost', user='root', password='123456', db='123456', charset='utf8')\n",
    "    cur = conn.cursor()\n",
    "    print('数据库连接成功！')\n",
    "    print(' ')\n",
    "except:\n",
    "    print('数据库连接失败！')\n",
    "\n",
    "os.chdir('..\\chinascope_news_data/')  #将路径设置成你csv文件放的地方\n",
    "path = os.getcwd()\n",
    "files = os.listdir(path)\n",
    "\n",
    "i = 0  #计数器，后面可以用来统计一共导入了多少个文件\n",
    "for file in files:\n",
    "    if file.split('.')[-1] in ['csv']:  #判断文件是不是csv文件，file.split('.')[-1]获取‘.’后的字符串\n",
    "        i += 1\n",
    "        filename = file.split('.')[0]  #获取剔除后缀的名称\n",
    "        filename = 'data_' + filename\n",
    "        f = pd.read_csv(file, encoding='gbk')  #用pandas读取文件，得到pandas框架格式的数据\n",
    "        columns = f.columns.tolist()  #获取表格数据内的列标题文字数据\n",
    "\n",
    "        types = f.dtypes  #获取文件内数据格式\n",
    "        field = []  #设置列表用来接收文件转换后的数据，为写入mysql做准备\n",
    "        table = []\n",
    "        char = []\n",
    "        for item in range(len(columns)):  #开始循环获取文件格式类型并将其转换成mysql文件格式类型\n",
    "            if 'object' == str(types[item]):\n",
    "                char = '`' + columns[item] + '`' + ' VARCHAR(255)'  #必须加上`这个点，否则在写入mysql是会报错\n",
    "            elif 'int64' == str(types[item]):\n",
    "                char = '`' + columns[item] + '`' + ' INT'\n",
    "            elif 'float64' == str(types[item]):\n",
    "                char = '`' + columns[item] + '`' + ' FLOAT'\n",
    "            elif 'datetime64[ns]' == str(types[item]):\n",
    "                char = '`' + columns[item] + '`' + ' DATETIME'\n",
    "            else:\n",
    "                char = '`' + columns[item] + '`' + ' VARCHAR(255)'\n",
    "            table.append(char)\n",
    "            field.append('`' + columns[item] + '`')\n",
    "\n",
    "        tables = ','.join(table)  #将table中的元素用，连接起来为后面写入mysql做准备\n",
    "        fields = ','.join(field)\n",
    "\n",
    "        cur.execute('drop table if exists {};'.format(filename))\n",
    "        conn.commit()\n",
    "\n",
    "        #创建表格并设置表格的列文字跟累数据格式类型\n",
    "        table_sql = 'CREATE TABLE IF NOT EXISTS ' + filename + '(' + 'id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,' + tables + ');'\n",
    "        print('表:' + filename + ',开始创建数据表...')\n",
    "        cur.execute(table_sql)\n",
    "        conn.commit()\n",
    "        print('表:' + filename + ',创建成功!')\n",
    "\n",
    "        print('表:' + filename + ',正在写入数据当中...')\n",
    "        f_sql = f.astype(object).where(pd.notnull(f), None)  #将原来从csv文件获取得到的空值数据设置成None，不设置将会报错\n",
    "        values = f_sql.values.tolist()  #获取数值\n",
    "        s = ','.join(['%s' for _ in range(len(f.columns))])  #获得文件数据有多少列，每个列用一个 %s 替代\n",
    "        insert_sql = 'insert into {}({}) values({})'.format(filename,fields,s)\n",
    "        cur.executemany(insert_sql, values)\n",
    "        conn.commit()\n",
    "        print('表:' + filename + ',数据写入完成！')\n",
    "        print(' ')\n",
    "cur.close()\n",
    "conn.close()\n",
    "print('文件导入数据库完成！一共导入了 {} 个CSV文件。'.format(i))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
